﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// MsSqlDao 的摘要说明
/// </summary>
public class MsSqlDao
{
	public MsSqlDao()
	{
		//
		// TODO: 在此处添加构造函数逻辑
		//
	}
    /// <summary>
    /// 查询数据库中的数据并存入数据表中
    /// </summary>
    /// <param name="conn"></param>
    /// <param name="sql"></param>
    /// <returns>返回一个数据表</returns>
    public DataTable FindAll(SqlConnection conn,string sql)
    {
        SqlCommand cmd = new SqlCommand(sql, conn);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds, "bb");
        DataTable dt = ds.Tables["bb"];
        return dt;
    }
    /// <summary>
    /// 将数据表插入到数据库中
    /// </summary>
    /// <param name="conn"></param>
    /// <param name="sql"></param>
    /// <param name="item"></param>
    /// <param name="i"></param>
    /// <returns></returns>
    public int Insert(SqlConnection conn,string sql,DataRow item,int i)
    {
        string p0 = item["月份"].ToString();
        string p1 = item["员工编号"].ToString();
        string p2 = item["姓名"].ToString();
        string p3 = item["入司时间"].ToString();
        string p4 = item["部门"].ToString();
        string p5 = item["身份证号码"].ToString();
        string p6 = item["个人绩效"].ToString();
        string p7 = item["提成"].ToString();
        string p8 = item["其他工资"].ToString();
        string p9 = item["水电费"].ToString();
        string p10 = item["其它代扣"].ToString();
        string p11 = item["加班天数"].ToString();
        string p12 = item["加班时长"].ToString();
        string p13 = item["其它加班"].ToString();
        string p14 = item["迟到"].ToString();
        string p15 = item["早退"].ToString();
        string p16 = item["上班未打卡"].ToString();
        string p17 = item["下班未打卡"].ToString();        
        string p18 = item["事假"].ToString();
        string p19 = item["病假"].ToString();
        string p20 = item["年假"].ToString();
        string p21 = item["其他应扣"].ToString();        


        SqlCommand cmd = new SqlCommand(sql, conn);
        SqlParameter[] pm = new SqlParameter[22];

        pm[0] = new SqlParameter("@p0", p0);
        pm[1] = new SqlParameter("@p1", p1);
        pm[2] = new SqlParameter("@p2", p2);
        pm[3] = new SqlParameter("@p3", p3);
        pm[4] = new SqlParameter("@p4", p4);
        pm[5] = new SqlParameter("@p5", p5);
        pm[6] = new SqlParameter("@p6", p6);
        pm[7] = new SqlParameter("@p7", p7);
        pm[8] = new SqlParameter("@p8", p8);
        pm[9] = new SqlParameter("@p9", p9);
        pm[10] = new SqlParameter("@p10", p10);
        pm[11] = new SqlParameter("@p11", p11);
        pm[12] = new SqlParameter("@p12", p12);
        pm[13] = new SqlParameter("@p13", p13);
        pm[14] = new SqlParameter("@p14", p14);
        pm[15] = new SqlParameter("@p15", p15);
        pm[16] = new SqlParameter("@p16", p16);
        pm[17] = new SqlParameter("@p17", p17);
        pm[18] = new SqlParameter("@p18", p18);
        pm[19] = new SqlParameter("@p19", p19);
        pm[20] = new SqlParameter("@p20", p20);
        pm[21] = new SqlParameter("@p21", p21);

        foreach (SqlParameter item1 in pm)
        {
            cmd.Parameters.Add(item1);
        }
        i += cmd.ExecuteNonQuery();
        return i;
    }
    /// <summary>
    /// 将数据表插入到数据库中
    /// </summary>
    /// <param name="conn"></param>
    /// <param name="sql"></param>
    /// <param name="item"></param>
    /// <param name="i"></param>
    /// <returns></returns>
    public int InsertUserInfo(SqlConnection conn, string sql, DataRow item, int i)
    {
        string p0 = item["员工编号"].ToString();
        string p1 = item["人事编号"].ToString();
        string p2 = item["姓名"].ToString();
        string p3 = item["部门"].ToString();
        string p4 = item["性别"].ToString();
        string p5 = item["民族"].ToString();
        string p6 = item["出生日期"].ToString();
        string p7 = item["身份证号码"].ToString();
        string p8 = item["开户银行"].ToString();
        string p9 = item["银行账号"].ToString();
        string p10 = item["电话"].ToString();
        string p11 = item["保险类型"].ToString();
        string p12 = item["保险号码"].ToString();
        string p13 = item["学历"].ToString();
        string p14 = item["入司时间"].ToString();

        SqlCommand cmd = new SqlCommand(sql, conn);
        SqlParameter[] pm = new SqlParameter[15];

        pm[0] = new SqlParameter("@p0", p0);
        pm[1] = new SqlParameter("@p1", p1);
        pm[2] = new SqlParameter("@p2", p2);
        pm[3] = new SqlParameter("@p3", p3);
        pm[4] = new SqlParameter("@p4", p4);
        pm[5] = new SqlParameter("@p5", p5);
        pm[6] = new SqlParameter("@p6", p6);
        pm[7] = new SqlParameter("@p7", p7);
        pm[8] = new SqlParameter("@p8", p8);
        pm[9] = new SqlParameter("@p9", p9);
        pm[10] = new SqlParameter("@p10", p10);
        pm[11] = new SqlParameter("@p11", p11);
        pm[12] = new SqlParameter("@p12", p12);
        pm[13] = new SqlParameter("@p13", p13);
        pm[14] = new SqlParameter("@p14", p14);

        foreach (SqlParameter item1 in pm)
        {
            cmd.Parameters.Add(item1);
        }
        i += cmd.ExecuteNonQuery();
        return i;
    }
    public SqlDataReader FindAllReader(string sql,SqlConnection conn)
    {
        SqlCommand cmd = new SqlCommand(sql, conn);
        SqlDataReader dr = cmd.ExecuteReader();
        return dr;
    }
}